﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DA
{
    // khách hàng
    public class KhachHangDA :DBConnection
    {
        // hàm tạo
        public KhachHangDA()
        {
            sqlCommand.Connection = sqlConnection;
        }
        // lấy tất cả
        public DataSet GetAll()
        {
            var ds = new DataSet();
            const string query = "select * from KhachHang";
            if (open())
            {
                sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
                sqlDataAdapter.Fill(ds);
            }
            close();
            return ds;
        }
        public DataSet GetByID(int id)
        {
            var ds = new DataSet();
            string query = String.Format("select * from KHACHHANG where MaKH='{0}'", id);
            if (open())
            {
                sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
                sqlDataAdapter.Fill(ds);
            }
            close();
            return ds;
        }
        // lấy theo ngày
        public DataSet GetByDate(DateTime date)
        {
            string d = String.Format("{0}/{1}/{2}", date.Year, date.Month, date.Day);
            string query = String.Format("select MaKH,HoTen,SoDT,Email,DiaChi,CMND from KHACHHANG where NgayDangKy= '{0}'", d);
            var ds = new DataSet();
            if (open())
            {
                sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
                sqlDataAdapter.Fill(ds);
            }
            close();
            return ds;
        }
        // lấy mã khách hàng theo CMND
        public DataSet GetByCMND(string cmnd)
        {
            var ds = new DataSet();
            string query = String.Format("select MaKH from KHACHHANG where CMND='{0}'", cmnd.Trim());
            if (open())
            {
                sqlDataAdapter = new SqlDataAdapter(query, sqlConnection);
                sqlDataAdapter.Fill(ds);
            }
            close();
            return ds;
        }
        // thêm 
        public int Add(KhachHangDTO item)
        {
            var query = String.Format("insert into KhachHang values(N'{0}',N'{1}',N'{2}',N'{3}',N'{4}',N'{5}'); select SCOPE_IDENTITY();",
                item.HoTen, item.SoDT, item.Email,
                item.DiaChi, item.CMND, item.NgayDangKy);
           
            sqlCommand.CommandText = query;
            try
            {
                open();
                var idRe = -1;
                sqlCommand.CommandText = query;
                var re = sqlCommand.ExecuteScalar().ToString();
                idRe = Int32.Parse(re);
                close();
                return idRe;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return -1;
            }
        }
        // sửa
        public bool Update(KhachHangDTO item)
        {
            var query = String.Format("Update KhachHang set HoTen=N'{0}',DiaChi=N'{1}',Email=N'{2}',SoDT=N'{3}' where MaKH='{4}'",
                item.HoTen, item.DiaChi, item.Email, item.SoDT,
                item.MaKH);
           
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        // TODO chưa test
        // sửa khách hàng đã tồn tại CMND
        public bool UpdateKhachHangDaTonTaiCMND(KhachHangDTO item)
        {
            var query = String.Format("Update KhachHang set HoTen=N'{0}',DiaChi=N'{1}',Email=N'{2}',SoDT=N'{3}',NgayDangKy=N'{4}' where CMND='{5}'",
                item.HoTen, item.DiaChi, item.Email, item.SoDT, 
                item.NgayDangKy, item.CMND);
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        // xóa 
        public bool Delete(int ma_item)
        {
            var query = "Delete from KhachHang where MaKhachHang = " + ma_item;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                sqlCommand.Parameters.Clear();
                close();
                return true;
            }
            catch (SqlException s)
            {
                Console.WriteLine(s.Message);
                return false;
            }
        }
        public DataSet getThongtinDiaOc(string _makh)
        {
            string query = String.Format("select KHACHHANG.MaKH,HoTen,SoDT,Email,DiaChi,MaDiaOc,TenLoaiDiaOc,DiaChiDiaOc,DienTichDat,DienTichXayDung,Huong,ViTri,MoTa,(CASE TrangThaiBan WHEN 1 THEN N'Đã Bán' else N'Chưa Bán' END) as TrangThaiBan,GiaBan,GiayPhepXayDung,GiayPhepHoanCong,GiayChungNhanQuyenSuDungDat,GiayChungNhanSoHuuNha,HopDongMuaban,(CASE TrangThaiDangKy WHEN 1 THEN N'Đã Đăng Ký' else N'Chưa Đăng Ký' END) as TrangThaiDangKy from KHACHHANG inner join DIAOC on KHACHHANG.MaKH=DIAOC.MaKH inner join LOAIDIAOC on DIAOC.MaLoaiDiaOc=LOAIDIAOC.MaLoaiDiaOc where KHACHHANG.MaKH = '{0}'", Int32.Parse(_makh));
            return new MyQueryDA().doQuery(query);
        }
    }
}
